﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

namespace Repository
{
    public class TargetFormRepository : ITargetFormRepository, IDisposable
    {
        private readonly IDbConnection _dbConnection;
        private bool _disposed = false;

        public TargetFormRepository(IConfiguration configuration)
        {
            string connectionString = configuration.GetConnectionString("DefaultConnection");
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("数据库连接字符串 'DefaultConnection' 未配置");
            }
            _dbConnection = new SqlConnection(connectionString);
            _dbConnection.Open();
        }

        /// <summary>
        /// 获取所有可用的目标表单
        /// </summary>
        public List<TargetForm> GetAll()
        {
            string sql = "SELECT target_id, target_form, is_active, creation_time FROM sys_target_form_table WHERE is_active = 1";
            try
            {
                return _dbConnection.Query<TargetForm>(sql).AsList();
            }
            catch (SqlException ex)
            {
                throw new Exception("查询目标表单列表失败", ex);
            }
        }

        /// <summary>
        /// 根据ID获取目标表单
        /// </summary>
        public TargetForm GetById(int target_id)
        {
            if (target_id <= 0)
                throw new ArgumentOutOfRangeException(nameof(target_id), "目标表单ID必须大于0");

            string sql = "SELECT target_id, target_form, is_active, creation_time FROM sys_target_form_table WHERE target_id = @target_id AND is_active = 1";
            try
            {
                return _dbConnection.QueryFirstOrDefault<TargetForm>(sql, new { target_id });
            }
            catch (SqlException ex)
            {
                throw new Exception($"查询ID为{target_id}的目标表单失败", ex);
            }
        }

        /// <summary>
        /// 根据名称获取目标表单ID
        /// </summary>
        public int? GetTargetIdByName(string targetFormName)
        {
            if (string.IsNullOrWhiteSpace(targetFormName))
                return null;

            string sql = "SELECT target_id FROM sys_target_form_table WHERE target_form = @target_form_name AND is_active = 1";
            try
            {
                return _dbConnection.QueryFirstOrDefault<int?>(sql, new { target_form_name = targetFormName });
            }
            catch (SqlException ex)
            {
                throw new Exception($"根据名称获取目标表单ID失败: {targetFormName}", ex);
            }
        }

        /// <summary>
        /// 根据表单名称获取ID
        /// </summary>
        public int GetIdByFormName(string target_form)
        {
            if (string.IsNullOrWhiteSpace(target_form))
                throw new ArgumentException("目标表单名称不能为空", nameof(target_form));

            string sql = "SELECT target_id FROM sys_target_form_table WHERE target_form = @target_form AND is_active = 1";
            return _dbConnection.ExecuteScalar<int>(sql, new { target_form });
        }

        /// <summary>
        /// 检查目标表单名称是否已存在（忽略空格和大小写）
        /// </summary>
        public bool ExistsByFormName(string targetFormName)
        {
            if (string.IsNullOrWhiteSpace(targetFormName))
                return false;

            string sql = @"
            SELECT COUNT(1) 
            FROM sys_target_form_table 
            WHERE LOWER(LTRIM(RTRIM(target_form))) = LOWER(LTRIM(RTRIM(@target_form_name))) 
              AND is_active = 1";

            int count = _dbConnection.ExecuteScalar<int>(sql, new { target_form_name = targetFormName });
            return count > 0;
        }

        /// <summary>
        /// 【修复】根据表单名称异步查询目标表单（匹配接口返回类型）
        /// </summary>
        public async Task<TargetForm> GetByFormNameAsync(string formName)
        {
            if (string.IsNullOrWhiteSpace(formName))
                throw new ArgumentException("表单名称不能为空", nameof(formName));

            string sql = "SELECT target_id, target_form, is_active, creation_time FROM sys_target_form_table WHERE target_form = @formName AND is_active = 1";
            try
            {
                return await _dbConnection.QueryFirstOrDefaultAsync<TargetForm>(sql, new { formName });
            }
            catch (SqlException ex)
            {
                throw new Exception($"根据名称查询目标表单失败: {formName}", ex);
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing && _dbConnection != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                    _dbConnection.Dispose();
                }
                _disposed = true;
            }
        }

        ~TargetFormRepository()
        {
            Dispose(false);
        }
    }
}